﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Linq;
using System.Windows.Forms;
using DevExpress.XtraEditors;
using System.Configuration;
using System.Data.SqlClient;
using SHelper;
using DevExpress.XtraSplashScreen;
using DevExpress.XtraWaitForm;
using System.IO;

namespace InvoiceImport
{
    public partial class ReportForm2 : DevExpress.XtraEditors.XtraForm
    {
        public ReportForm2()
        {
            InitializeComponent();
        }

        string con = ConfigurationManager.ConnectionStrings["conn"].ConnectionString;
        public string ServerText { get; set; }
        public MainFrm mf { get; set; }
        DataTable SRtable, CBtable, Table1;
        string sdate1 = "", edate1 = "", sdate2 = "", edate2 = "", ItemName1 = "", ItemName2 = "", Lsdate = "", Ledate = "", Tsdate = "", Tedate = "";
        //行号
        private void gv_CustomDrawRowIndicator(object sender, DevExpress.XtraGrid.Views.Grid.RowIndicatorCustomDrawEventArgs e)
        {
            if (e.Info.IsRowIndicator)
            {
                e.Info.DisplayText = (e.RowHandle + 1).ToString();
            }
        }
        //查询
        private void SelectData()
        {
            var wf = new SplashScreenManager(this, typeof(DemoWaitForm), true, true);//进程
            wf.ShowWaitForm();
            wf.SetWaitFormCaption("正在查询...");
            #region 查询条件赋值
            sdate1 = string.Format("and d.dPBVDate>='{0}-{1}-01'", cYear.Text,cMonth.Text);
            sdate2 = string.Format("and b.dDate>='{0}-{1}-01'", cYear.Text,cMonth.Text);
            edate1 = string.Format("and d.dPBVDate<='{0}-{1}-{2}'", cYear.Text,cMonth.Text,DateTime.DaysInMonth(int.Parse(cYear.Text),int.Parse(cMonth.Text)).ToString("00"));
            edate2 = string.Format("and b.dDate<='{0}-{1}-{2}'", cYear.Text,cMonth.Text,DateTime.DaysInMonth(int.Parse(cYear.Text),int.Parse(cMonth.Text)).ToString("00"));
            Lsdate = DateTime.Parse(string.Format("{0}-{1}-01",cYear.Text,cMonth.Text)).AddMonths(-1).ToShortDateString();
            Ledate = DateTime.Parse(string.Format("{0}-{1}-01",cYear.Text,cMonth.Text)).AddDays(-1).ToShortDateString();
            Tsdate = DateTime.Parse(string.Format("{0}-{1}-01", cYear.Text, cMonth.Text)).ToShortDateString();
            Tedate = DateTime.Parse(string.Format("{0}-{1}-01", cYear.Text, cMonth.Text)).AddMonths(1).AddDays(-1).ToShortDateString();
            #endregion
            DataTable MappingTable = SqlHelper.ExecuteDataTable("select * from ufsystem.dbo.AreaMapping order by aName", con, new SqlParameter());
            MappingTable.Columns.Add("收入本币无税金额");
            MappingTable.Columns.Add("上月预估收入");
            MappingTable.Columns.Add("当月预估收入");
            MappingTable.Columns.Add("不含税收入含预估");
            MappingTable.Columns.Add("成本本币无税金额");
            MappingTable.Columns.Add("上月预估成本");
            MappingTable.Columns.Add("当月预估成本");
            MappingTable.Columns.Add("不含税成本含预估");
            MappingTable.Columns.Add("本币无税利润");
            MappingTable.Columns.Add("上月预估利润");
            MappingTable.Columns.Add("当月预估利润");
            MappingTable.Columns.Add("不含税利润含预估");
            for (int i = 0; i < MappingTable.Rows.Count; i++)
            {
                MappingTable.Rows[i]["收入本币无税金额"] = SqlHelper.ExecuteScalar(string.Format(@"select sum(a.iNatMoney) [收入本币无税金额]
                from salebillvouchs as a left join salebillvouch  as b on a.SBVID=b.SBVID
                where a.cInvCode ='{0}' {1} {2}", MappingTable.Rows[i]["aCode"], sdate2, edate2), con, new SqlParameter());
                if (string.IsNullOrEmpty(MappingTable.Rows[i]["收入本币无税金额"].ToString())) MappingTable.Rows[i]["收入本币无税金额"] = 0;
                MappingTable.Rows[i]["上月预估收入"] = SqlHelper.ExecuteScalar(string.Format(@"select (sum(md)-sum(mc))[上月预估收入] from gl_accvouch where dbill_date>='{0}' and dbill_date <='{1}' and (ccode ='113104' or ccode='510107' and cdept_id='{2}' and citem_id='{3}')"
                    , Lsdate, Ledate, MappingTable.Rows[i]["aName"], MappingTable.Rows[i]["aCode"]), con, new SqlParameter());
                if (string.IsNullOrEmpty(MappingTable.Rows[i]["上月预估收入"].ToString())) MappingTable.Rows[i]["上月预估收入"] = 0;
                MappingTable.Rows[i]["当月预估收入"] = SqlHelper.ExecuteScalar(string.Format(@"select (sum(md)-sum(mc))[当月预估收入] from gl_accvouch where dbill_date>='{0}' and dbill_date <='{1}' and (ccode ='113104' or ccode='510107' and cdept_id='{2}' and citem_id='{3}')"
                    , Tsdate, Tedate, MappingTable.Rows[i]["aName"], MappingTable.Rows[i]["aCode"]), con, new SqlParameter());
                if (string.IsNullOrEmpty(MappingTable.Rows[i]["当月预估收入"].ToString())) MappingTable.Rows[i]["当月预估收入"] = 0;
                MappingTable.Rows[i]["不含税收入含预估"] = Math.Round(double.Parse(MappingTable.Rows[i]["收入本币无税金额"].ToString()) + double.Parse(MappingTable.Rows[i]["当月预估收入"].ToString()), 2);
                MappingTable.Rows[i]["成本本币无税金额"] = SqlHelper.ExecuteScalar(string.Format(@"select sum(c.iMoney) [成本本币无税金额]
                from purbillvouchs as c left join purbillvouch  as d on c.PBVID=d.PBVID
                where c.cInvCode ='{0}' {1} {2}", MappingTable.Rows[i]["aCode"], sdate1, edate1), con, new SqlParameter());
                if (string.IsNullOrEmpty(MappingTable.Rows[i]["成本本币无税金额"].ToString())) MappingTable.Rows[i]["成本本币无税金额"] = 0;
                MappingTable.Rows[i]["上月预估成本"] = SqlHelper.ExecuteScalar(string.Format(@"select (sum(mc)-sum(md))[上月预估成本] from gl_accvouch where dbill_date>='{0}' and dbill_date <='{1}' and (ccode ='540103' or ccode='212104' and cdept_id='{2}' and citem_id='{3}')"
                    , Lsdate, Ledate, MappingTable.Rows[i]["aName"], MappingTable.Rows[i]["aCode"]), con, new SqlParameter());
                if (string.IsNullOrEmpty(MappingTable.Rows[i]["上月预估成本"].ToString())) MappingTable.Rows[i]["上月预估成本"] = 0;
                MappingTable.Rows[i]["当月预估成本"] = SqlHelper.ExecuteScalar(string.Format(@"select (sum(mc)-sum(md))[上月预估成本] from gl_accvouch where dbill_date>='{0}' and dbill_date <='{1}' and (ccode ='540103' or ccode='212104' and cdept_id='{2}' and citem_id='{3}')"
                    , Tsdate, Tedate, MappingTable.Rows[i]["aName"], MappingTable.Rows[i]["aCode"]), con, new SqlParameter());
                if (string.IsNullOrEmpty(MappingTable.Rows[i]["当月预估成本"].ToString())) MappingTable.Rows[i]["当月预估成本"] = 0;
                MappingTable.Rows[i]["不含税成本含预估"] = Math.Round(double.Parse(MappingTable.Rows[i]["成本本币无税金额"].ToString()) + double.Parse(MappingTable.Rows[i]["当月预估成本"].ToString()), 2);
                MappingTable.Rows[i]["本币无税利润"] = Math.Round(double.Parse(MappingTable.Rows[i]["收入本币无税金额"].ToString()) - double.Parse(MappingTable.Rows[i]["成本本币无税金额"].ToString()), 2);
                MappingTable.Rows[i]["上月预估利润"] = Math.Round(double.Parse(MappingTable.Rows[i]["上月预估收入"].ToString()) - double.Parse(MappingTable.Rows[i]["上月预估成本"].ToString()), 2);
                MappingTable.Rows[i]["当月预估利润"] = Math.Round(double.Parse(MappingTable.Rows[i]["当月预估收入"].ToString()) - double.Parse(MappingTable.Rows[i]["当月预估成本"].ToString()), 2);
                MappingTable.Rows[i]["不含税利润含预估"] = Math.Round(double.Parse(MappingTable.Rows[i]["不含税收入含预估"].ToString()) - double.Parse(MappingTable.Rows[i]["不含税成本含预估"].ToString()), 2);
            }
            gc.DataSource = MappingTable;
            if (wf.IsSplashFormVisible) wf.CloseWaitForm();//关闭进程
            return;

            
            #region 添加项目合计
            string tempItemNmae="";
            int r = Table1.Rows.Count;
            for (int i = 0; i < r; i++)
            {
                if (tempItemNmae != Table1.Rows[i]["项目名称"].ToString().Trim())
                {
                    tempItemNmae = Table1.Rows[i]["项目名称"].ToString().Trim();
                    DataRow dr = Table1.NewRow();
                    dr["部门"] = Table1.Rows[i]["部门"].ToString().Trim();
                    dr["项目名称"] = Table1.Rows[i]["项目名称"].ToString().Trim();
                    dr["存货编码"] = "合计";
                    dr["成本原币无税金额"] = GetSum(Table1.Select(string.Format("项目名称='{0}'", tempItemNmae)), "成本原币无税金额");
                    dr["成本原币价税合计"] = GetSum(Table1.Select(string.Format("项目名称='{0}'", tempItemNmae)), "成本原币价税合计");
                    dr["成本本币无税金额"] = GetSum(Table1.Select(string.Format("项目名称='{0}'", tempItemNmae)), "成本本币无税金额");
                    dr["成本本币价税合计"] = GetSum(Table1.Select(string.Format("项目名称='{0}'", tempItemNmae)), "成本本币价税合计");
                    dr["收入原币无税金额"] = GetSum(Table1.Select(string.Format("项目名称='{0}'", tempItemNmae)), "收入原币无税金额");
                    dr["收入原币价税合计"] = GetSum(Table1.Select(string.Format("项目名称='{0}'", tempItemNmae)), "收入原币价税合计");
                    dr["收入本币无税金额"] = GetSum(Table1.Select(string.Format("项目名称='{0}'", tempItemNmae)), "收入本币无税金额");
                    dr["收入本币价税合计"] = GetSum(Table1.Select(string.Format("项目名称='{0}'", tempItemNmae)), "收入本币价税合计");
                    Table1.Rows.Add(dr);
                }
            }
            #endregion
            #region 添加利润合计
            Table1.Columns.Add("原币无税利润", Type.GetType("System.Decimal"));
            Table1.Columns.Add("原币含税利润", Type.GetType("System.Decimal"));
            Table1.Columns.Add("本币无税利润", Type.GetType("System.Decimal"));
            Table1.Columns.Add("本币含税利润", Type.GetType("System.Decimal"));
            for (int i = 0; i < Table1.Rows.Count; i++)
            {
                Table1.Rows[i]["原币无税利润"] = Math.Round((double.Parse(Table1.Rows[i]["收入原币无税金额"].ToString()) - double.Parse(Table1.Rows[i]["成本原币无税金额"].ToString())), 2).ToString("0.00");
                Table1.Rows[i]["原币含税利润"] = Math.Round((double.Parse(Table1.Rows[i]["收入原币价税合计"].ToString()) - double.Parse(Table1.Rows[i]["成本原币价税合计"].ToString())), 2).ToString("0.00");
                Table1.Rows[i]["本币无税利润"] = Math.Round((double.Parse(Table1.Rows[i]["收入本币无税金额"].ToString()) - double.Parse(Table1.Rows[i]["成本本币无税金额"].ToString())), 2).ToString("0.00");
                Table1.Rows[i]["本币含税利润"] = Math.Round((double.Parse(Table1.Rows[i]["收入本币价税合计"].ToString()) - double.Parse(Table1.Rows[i]["成本本币价税合计"].ToString())), 2).ToString("0.00");
            }
            #endregion
            #region 添加总合计
            DataView dv = Table1.DefaultView;
            dv.Sort = "项目名称 Asc";
            DataTable Table2 = dv.ToTable();
            if (r > 0)
            {
                DataRow dr = Table2.NewRow();
                dr["存货编码"] = "总合计";
                dr["成本原币无税金额"] = GetSum(Table1.Select(""), "成本原币无税金额");
                dr["成本原币价税合计"] = GetSum(Table1.Select(""), "成本原币价税合计");
                dr["成本本币无税金额"] = GetSum(Table1.Select(""), "成本本币无税金额");
                dr["成本本币价税合计"] = GetSum(Table1.Select(""), "成本本币价税合计");
                dr["收入原币无税金额"] = GetSum(Table1.Select(""), "收入原币无税金额");
                dr["收入原币价税合计"] = GetSum(Table1.Select(""), "收入原币价税合计");
                dr["收入本币无税金额"] = GetSum(Table1.Select(""), "收入本币无税金额");
                dr["收入本币价税合计"] = GetSum(Table1.Select(""), "收入本币价税合计");
                dr["原币无税利润"] = GetSum(Table1.Select(""), "收入原币无税金额");
                dr["原币含税利润"] = GetSum(Table1.Select(""), "收入原币价税合计");
                dr["本币无税利润"] = GetSum(Table1.Select(""), "收入本币无税金额");
                dr["本币含税利润"] = GetSum(Table1.Select(""), "收入本币价税合计");
                Table2.Rows.Add(dr);
            }
            #endregion
            gc.DataSource = MappingTable;
            if (wf.IsSplashFormVisible) wf.CloseWaitForm();//关闭进程
        }
        //获取部门ID
        public string GetDeptID(string DepartName)
        {
            DataTable Dept = SqlHelper.ExecuteDataTable(string.Format("select cDepCode from department where cDepName='{0}'", DepartName), con, new SqlParameter());
            return Dept.Rows[0][0].ToString();
        }
        //获取合计
        public string GetSum(DataRow[] drs, string ColumnName)
        {
            double sum = 0;
            foreach (var temp in drs)
            {
                try
                {
                    sum += double.Parse(temp[ColumnName].ToString());
                }
                catch { sum += 0; }
            }
            return Math.Round(sum, 2).ToString("0.00");
        }
        //加载
        private void ReportForm_Load(object sender, EventArgs e)
        {
            DateTime tempDate = DateTime.Today.AddMonths(-1);
            cYear.Items.Add(DateTime.Today.Year - 5);
            cYear.Items.Add(DateTime.Today.Year - 4);
            cYear.Items.Add(DateTime.Today.Year - 3);
            cYear.Items.Add(DateTime.Today.Year - 2);
            cYear.Items.Add(DateTime.Today.Year - 1);
            cYear.Items.Add(DateTime.Today.Year);
            cYear.Items.Add(DateTime.Today.Year + 1);
            cYear.Items.Add(DateTime.Today.Year + 2);
            cYear.Items.Add(DateTime.Today.Year + 3);
            cYear.Items.Add(DateTime.Today.Year + 4);
            cYear.Items.Add(DateTime.Today.Year + 5);
            cYear.Text = DateTime.Today.Year.ToString("0000");
            cMonth.Text = DateTime.Today.Month.ToString("00");
            con = con.Replace("UFDATA_999_2013", ServerText);
        }
        //查询按钮
        private void SelectBtn_Click(object sender, EventArgs e)
        {
            SelectData();
        }
        //回车查询
        private void ItemText_KeyDown(object sender, KeyEventArgs e)
        {
            if (e.KeyCode == Keys.Enter)
            {
                SelectData();
            }
        }
        //导出Excel
        private void ExPortBtn_Click(object sender, EventArgs e)
        {
            if (gv.RowCount < 1)
            {
                MessageBox.Show("没有需要导出的数据!", "提示");
                return;
            }
            SaveFileDialog dialog = new SaveFileDialog()
            {
                FileName = string.Format("收入成本预估报表{0}-{1}-{2}", DateTime.Today.Year, DateTime.Today.Month, DateTime.Today.Day),
                Filter = "Execl 文件(*.xls)|*.xls|Execl 文件(*.xlsx)|*.xlsx"
            };
            FileInfo KGFileInfo = null;
            FileInfo KGFileInfo1 = null;
            if (dialog.ShowDialog() != DialogResult.OK)
            {
                return;
            }
            else
            {
                KGFileInfo = new FileInfo(dialog.FileName);
                KGFileInfo1 = new FileInfo(dialog.FileName);
            }
            try
            {
                switch (KGFileInfo.Extension.ToLower())
                {
                    case ".xls":
                        gv.ExportToXls(dialog.FileName);
                        break;
                    case ".xlsx":
                        gv.ExportToXlsx(dialog.FileName);
                        break;
                    default:
                        MessageBox.Show(string.Format("尚未支持的格式{0}", KGFileInfo.Extension), "提示");
                        return;
                }
                XtraMessageBox.Show("导出成功!", "提示");
            }
            catch (Exception ex)
            {
                XtraMessageBox.Show("导出失败!原因:" + ex, "提示");
            }
        }

        private void AreaSetting_Click(object sender, EventArgs e)
        {
            AreaSettingForm asf = new AreaSettingForm();
            asf.ShowDialog();
        }

        private void ReportForm_FormClosed(object sender, FormClosedEventArgs e)
        {
            mf.Show();
        }
    }
}